<?php

class ResultCertController extends BaseController {

    protected $hoc_vien;

    public function __construct(Hoc_vien $hoc_vien) {
        $this->hoc_vien = $hoc_vien;
    }

    public function getIndex() {
        $model_hoc_vien = new Hoc_vien();
        $hoc_viens = $model_hoc_vien->ds_hocvien_dau();

        return View::make('van_bangs.index', compact('hoc_viens'));
    }

    public function getCreate() {
        return View::make('van_bangs.create');
    }

    public function getEdit($id) {
        $hoc_vien = $this->hoc_vien->find($id);
        $khoa_thi = Khoa_thi::all();
        $hoi_dong_thi = Hoi_dong_thi::all();
        $chung_chi = Chung_chi::all();
        if (is_null($hoc_vien)) {
            $url = asset('resultcerts');
            header('Location: ' . $url);
            exit();
        }
        return View::make('van_bangs.edit', compact('hoc_vien'))
                        ->with('chung_chi', $chung_chi)
                        ->with('khoa_thi', $khoa_thi);
    }
 

    public function getDestroy($id) {
        $hoc_vien = $this->hoc_vien->find($id);
        return $hoc_vien;
//        $this->hoc_vien->find($id)->delete();
        //return Redirect::route('students.index');
    }

    public function postReadexcel() {
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;
        //Đọc file excel
        Excel::load($inputFileName, function($reader) {
            $results = $reader->all();
            //Khởi tạo ds mã học viên
            //in table
            $html = '<table class="table table-bordered" >';
            $html .= "<tr style='background: rgb(243, 243, 243);'>";
            $html .= "<td>Mã Học Viên</td>";
            $html .= "<td>SBD</td>";
            $html .= "<td>Mã Khoá Thi</td>";
            $html .= "<td>Mã Chứng Chỉ</td>";
            $html .= "<td>Kết Quả</td>";
            $html .= "<td>Số Hiệu Bằng</td>";
            $html .= "<td>Số Sổ Cấp Chứng Chỉ</td>";
            $html .= "<td>Số Quyết Định Tốt Nghiệp</td></tr>";
            // $html .= $results;
            $flat_err = 0;
            foreach ($results as $results) {
                $ket_qua = DB::table('hoc_viens')
                        ->leftJoin('khoa_this', 'hoc_viens.KhoaThi_Id', '=', 'khoa_this.id')
                        ->leftJoin('chung_chis', 'hoc_viens.ChungChi_Id', '=', 'chung_chis.id')
                        ->where('hoc_viens.sbd', '=', $results->sbd)
                        ->where('khoa_this.ma_khoa_thi', '=', $results->ma_khoa_thi)
                        ->pluck('hoc_viens.ket_qua');

                if ($ket_qua == '1') {
                    $html .= "<tr>";
                    $html .= "<td>" . $results->ma_hv . "</td>";
                    $html .= "<td>" . $results->sbd . "</td>";
                    $html .= "<td>" . $results->ma_khoa_thi . "</td>";
                    $html .= "<td>" . $results->ma_chung_chi . "</td>";
			        $html .= "<td>Đạt</td>";
                    $html .= "<td>" . $results->so_hieu_bang . "</td>";
                    $html .= "<td>" . $results->so_so_cap_chung_chi . "</td>";
                    $html .= "<td>" . $results->so_quyet_dinh_tot_nghiep . "</td>";
                    $html .= "</tr>";
                } elseif ($ket_qua == '0') {
                    $flat_err++;
                    $html .= "<tr style='background:yellow;'>";
                    $html .= "<td>" . $results->ma_hv . "</td>";
                    $html .= "<td>" . $results->sbd . "</td>";
                    $html .= "<td>" . $results->ma_khoa_thi . "</td>";
                    $html .= "<td>" . $results->ma_chung_chi . "</td>";
			        $html .= "<td>Không Đạt</td>";
                    $html .= "<td>" . $results->so_hieu_bang . "</td>";
                    $html .= "<td>" . $results->so_so_cap_chung_chi . "</td>";
                    $html .= "<td>" . $results->so_quyet_dinh_tot_nghiep . "</td>";
                    $html .= "</tr>";
                } else {
                    $flat_err++;
                    $html .= "<tr style='background:red;'>";
                    $html .= "<td>" . $results->ma_hv . "</td>";
                    $html .= "<td>" . $results->sbd . "</td>";
                    $html .= "<td>" . $results->ma_khoa_thi . "</td>";
                    $html .= "<td>" . $results->ma_chung_chi . "</td>";
                    $html .= "<td>Không Đạt</td>";
                    $html .= "<td>" . $results->so_hieu_bang . "</td>";
                    $html .= "<td>" . $results->so_so_cap_chung_chi . "</td>";
                    $html .= "<td>" . $results->so_quyet_dinh_tot_nghiep . "</td>";
                    $html .= "</tr>";
                }
            }
            $html .= "</table>";
            $html .="<div id='flats-err' hidden>" . $flat_err . "</div>";

            echo $html;
        });
    }

    public function postStoredsvanbang() {
        $input = Input::get('namefile');
        $inputFileName = "public/admin/upload_excel/server/php/files/" . $input;

        Excel::load($inputFileName, function($reader) {
            $results = $reader->all();
            foreach ($results as $results) {
                $khoa_thi_id = DB::table('khoa_this')->where('ma_khoa_thi', $results->ma_khoa_thi)->pluck('id');
                $id_hoc_vien = DB::table('hoc_viens')
                        ->where('sbd', '=', $results->sbd)
                        ->where('KhoaThi_Id', '=', $khoa_thi_id)
                        ->pluck('id');
                $input = array(
                    'so_hieu_bang' => $results->so_hieu_bang,
                    'so_so_cap_chung_chi' => $results->so_so_cap_chung_chi,
                    'so_quyet_dinh_tot_nghiep' => $results->so_quyet_dinh_tot_nghiep
                );
                DB::table('hoc_viens')->where('id', $id_hoc_vien)->update($input);
            }
        });
    }

}
